0除算でもNULL除算でも0を返すSnowflakeの除算関数「DIV0NULL」を試してみた #SnowflakeDB
※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の９日目の記事となります。
さがらです。
2022年11月のアップデートで、DIV0NULLという関数が追加されました。
本記事ではこの関数を試してみたので、その内容をまとめてみます。
DIV0NULL関数とは
一言でいうと、除算（割り算）を行う場合、分母が「0」や「NULL」であってもエラーやNULLを返すことなく、「0」を返す関数です。
例えば、下図のようにselect 1/0を実行すると、「ゼロでの除算」というエラーメッセージが返ってきます。このエラーを回避するための関数が、DIV0NULL関数となります。
試してみた
下記の公式Docに記載のサンプルに沿って、DIV0NULL関数を試してみます。
使い方としては、引数の１つ目に分子、２つ目に分母、に当たる値を入れればOKです。
例えば、select 1/2とselect div0null(1, 2)は同じ結果を返します。
続いて、0除算を実行してみます。select 1/0の場合は前述の通りエラーメッセージが返ってきますが、select div0null(1, 0)の場合には、「0」が返ってきました。
また、NULL除算も試してみます。select 1/nullの場合はNULLが返ってきてしまいますが、select div0null(1, null)の場合には、「0」が返ってきました。
ちなみに、NULLを分子に持って来る場合、DIV0NULL関数はNULLを返すので注意です。
どういった時に使うのか？
この関数ですが、関数自体の説明と被ってしまうのですが、除算時に分母が0であろうとNULLであろうと、結果はエラーでもNULLでもなく「0」を返したい場合に使えます。
具体的には、除算結果に対してCASE句やWHERE句などで分岐や絞り込みを行うとき、結果にnullが紛れていると適切に処理できないケースがあると思います。そんな時に便利な関数だと感じました。
実はSnowflakeには、元々DIV0という関数がありました（いつからDIV0関数があったかは私もわかっていないのですが…）
DIV0関数ですが、0除算の場合は0を返し、NULL除算の場合はNULLを返す、という関数です。
また、分子にNULLがある場合はDIV0関数でもDIV0NULL関数でもNULLをを返してしまうのですが、そんなときはNVLという関数が使えます。
NVL関数は、第1引数に入れた結果がNULLの場合、第2引数に入れた結果を返す関数のため、nvl(null/1, 0)のように記述すると、分子がNULLであっても0を返してくれます。
また、NVL関数について、0除算の場合はエラーを返してしまうので、少し面倒ですがnvl(div0null(1,0), 0)のように記述してあげれば、エラーをなくした上で、NULLが返ってくることはなくなります。
以上を踏まえると、使い分けのイメージは以下の4パターンかと思います。
- 分母に0があったらエラーを返してほしい
- 一般的な除算である「1/0」のような表記で記述する
- 分母に0があったら0を返してほしいが、分母にNULLがある場合にはNULLを返してほしい
DIV0関数を使用する
- 分母に0やNULLがある場合でも、0を返してほしい
DIV0NULL関数を使用する
- 分子にnullがある場合でも、分母に0やNULLがある場合でも、0を返してほしい
NVL関数とDIV0NULL関数を併用し、nvl(div0null(1,0), 0)のように記述する
最後に
0除算でもNULL除算でも0を返すSnowflakeの除算関数DIV0NULLを試してみました。
思いの外、NULL周りを制御する関数が多く、奥が深い領域だな～と感じた検証でした。
